package com.heu.blood.common.utils;

/*
 * 解析excel工具类
 * */

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class ExcelUtil<T> {

    // 定义范型，这样就能传对应的实体类来封装excel数据
    private T t;

    public ExcelUtil(T t) {
        this.t = t;
    }

    /**
     *@ClassName: ExcelUtil
     *@Description: 传入excel解析excel文件，提取出信息
     *@Params: InputStream is, String fileName
     *@Return:
     */

    public List<Map> AnalysisExcel(InputStream is, String fileName) throws IOException {
        int maxColNum = 0;
        List<Map> list = new ArrayList<>();
        Workbook workbook = createWorkbookByExcelType(is, fileName); // 创建工作簿
        Sheet sheet = workbook.getSheetAt(0);
        Row row = null;
        // 获取最大行数
        int maxRowNum = sheet.getLastRowNum();
        // 获取第一行
        row = sheet.getRow(0);

        // 获取最大列数
        if (workbook instanceof HSSFWorkbook ){
            //如果是xls的话 列数不变
            maxColNum = row.getLastCellNum();
        }else {
            //如果是xlsx的话 列数-1
            maxColNum = row.getLastCellNum()-1;
        }
        List<String> linkedList = new LinkedList<>();
        Field[] declaredFields = t.getClass().getDeclaredFields();
        // 把需要封装的实体类的属性名存入arrayList
        for (int i = 1; i < declaredFields.length; i++) {
            String name = declaredFields[i].getName();
            linkedList.add(name);
        }

        // 循环遍历excel表格，把每条数据封装成 map集合，再放入list集合中
        for (int i = 1; i <= maxRowNum; i++) {
            Map<String, String> map = new HashMap<>();

            row = sheet.getRow(i);

            if (row != null){
                for (int j = 0; j < maxColNum; j++) {
                    String cellData = String.valueOf(getCellFormatValue(row.getCell(j)));
                    map.put(linkedList.get(j), cellData); // map 封装
                }
                list.add(map); // map存入list
            }
        }
        return list;
    }


    //根据传入的文件名后缀判断是xls还是xlsx
    public static Workbook createWorkbookByExcelType(InputStream inputStream,String fileName){
        Workbook wb = null;
        if(fileName == null){
            return null;
        }
        String extString = fileName.substring(fileName.lastIndexOf("."));
        InputStream is = null;
        try {
            is = inputStream;
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is); // 2003版本 .xls
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is); // 2007版本 .xlsx
            }else{
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return wb;
    }

    // 用于获取表格中的数据方法
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case NUMERIC:{
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        String dateString = sdf.format(cellValue);
                        // 操作日期值的代码
                        cellValue = dateString;
                        break;
                    }
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case STRING:{

                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

}

